package com.genius.mobile_page.pingjia.service.impl;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;

import javax.servlet.http.HttpSession;

import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.genius.adminmanager.other.entity.FillAddJson;
import com.genius.adminmanager.other.entity.ResponseJson;
import com.genius.adminmanager.other.entity.SessionJson;
import com.genius.adminmanager.user.entity.UserJson;
import com.genius.mobile_page.pingjia.service.PingjiaServiceAdapter;
import com.genius.unite_platform.meeting_info.entity.Meeting_infoEntity;
import com.genius.unite_platform.meeting_info_detail.entity.Meeting_info_detailEntity;
import com.genius.unite_platform.notice_info.entity.Notice_infoEntity;
import com.genius.unite_platform.work_plan_info.entity.Work_plan_infoEntity;
import com.genius.workflow.element.SQLBean;
import com.genius.workflow.page.PageResult;
import com.genius.workflow.page.SearchResult;
import com.genius.workflow.page.SqlLang;
import com.genius.workflow.util.ConnectionException;
import com.genius.workflow.util.DBClosedException;
import com.genius.workflow.util.DBTools;
import com.genius.workflow.util.DBTools2;
import com.genius.workflow.util.DBUtils;
import com.genius.workflow.util.GetNo;

@Service("PingjiaSelectService")
public class PingjiaSelectServiceImpl extends PingjiaServiceAdapter{
	@Override
	public JSONObject get_next(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
        String selectSB = "select groupId,name,pId from t_group where  status = 1 and groupId != '153' and pId = '"+json.getString("pId")+"' ";
        String headField = "*";
		SearchResult  searchResult = new SearchResult();
		JSONObject mergeJson = new JSONObject();
		String asc = "";
		String ascField = "name";
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
//			System.out.println(sql.getSourceSql());
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject get_user(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
        String selectSB = "select username userId,name,photo from t_user where status =1 and FIND_IN_SET('"+json.getString("pId")+"',groupId) ";
        String headField = "*";
		SearchResult  searchResult = new SearchResult();
		JSONObject mergeJson = new JSONObject();
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject get_search_user(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
        String selectSB = "select username userId,name,photo from t_user where status =1 and name like '%"+json.getString("name")+"%' and groupId != ''  ";
        String headField = "*";
		SearchResult  searchResult = new SearchResult();
		JSONObject mergeJson = new JSONObject();
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject get_title(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
        String selectSB = "SELECT * from t_group WHERE FIND_IN_SET(groupId,(select REPLACE(path,'/',',')path from t_group where groupId = "+json.getString("pId")+")) order by state";
        String headField = "*";
		SearchResult  searchResult = new SearchResult();
		JSONObject mergeJson = new JSONObject();
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject get_zuzhi(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
        String selectSB = " SELECT * from t_group WHERE FIND_IN_SET(groupId,(SELECT REPLACE ( path, '/', ',' ) path FROM t_group WHERE  FIND_IN_SET(groupId,(SELECT groupId FROM t_user WHERE username =  '"+json.getString("userid")+"' limit 1))AND groupId  NOT IN ( '1667','1668','134','1673') limit 1 )) " + 
        				  " union " + 
			        	  "SELECT * from t_group WHERE FIND_IN_SET(groupId,(SELECT groupId FROM t_user WHERE username = '"+json.getString("userid")+"' limit 1))  AND groupId  NOT IN ( '1667','1668','134','1673') "+
			        	  "order by state ";
        System.err.println("----"+selectSB);
        String headField = "*";
		SearchResult  searchResult = new SearchResult();
		JSONObject mergeJson = new JSONObject();
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject get_touxiang(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
        String selectSB = " select photo from t_user where username = '"+json.getString("userid")+"' limit 1";
        String headField = "*";
		SearchResult  searchResult = new SearchResult();
		JSONObject mergeJson = new JSONObject();
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject get_label_dict(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
        String selectSB = " SELECT id,name from dict_label where flag =0  and type= 'label'" ;
        String headField = "*";
		SearchResult  searchResult = new SearchResult();
		JSONObject mergeJson = new JSONObject();
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject fillid(JSONObject json, JSONObject response) {   
	try {
    		JSONObject addJson = new JSONObject();
    		String sql = "SELECT " + 
    				"	CONCAT( " + 
    				"		DATE_FORMAT( CURDATE( ), '%Y%m%d' ), " + 
    				"		IFNULL( LPAD( RIGHT ( max( id ), 4 ) + 1, 4, 0 ), '0001' )  " + 
    				"	) id  " + 
    				"FROM " + 
    				"	pingjia_info  " + 
    				"WHERE " + 
    				"	id LIKE concat( DATE_FORMAT( CURDATE( ), '%Y%m%d' ), '%' )";
			HashMap<String, String> sqlMap = new HashMap<>();
			JSONObject fillJson = new JSONObject();
			addJson = DBUtils.select(sql);
			JSONObject fillEditJson = DBTools.getFilling(sqlMap);
			fillJson.put(FillAddJson.field, addJson);
			fillJson.put(FillAddJson.select, fillEditJson);
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_Add_Data_Success);
			response.put(ResponseJson.data,fillJson);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Add_Fill_Data_Error);
		}
		return response;
	}
	@Override
	public JSONObject Add(JSONObject json, JSONObject response) {    	
    	// 先查询主键是否存在
    	try {
    		JSONObject infoJson = json.getJSONObject("infoData");
	    	String keywords = "id";
	    	String insertTable = "pingjia_info";
	    	String arrayTable = "pingjia_label_detail";
	    	String starTable = "pingjia_star_detail";
			JSONObject existJson = DBTools2.getIsExist(keywords,insertTable, infoJson);
			//这个单号已经杯使用,重新去最大值作为单号
			if(existJson != null){
				String sql = "select CONCAT(DATE_FORMAT(CURDATE(),'%Y%m%d'),IFNULL(LPAD(RIGHT(max(id),4)+1,4,0),'0001'))id from pingjia_info where id like concat(DATE_FORMAT(CURDATE(),'%Y%m%d'),'%')";
				JSONObject addJson = DBUtils.select(sql);
				infoJson.put("id", addJson.getString("id"));
			}
			String insertField ="id,pjId,type,star,content,pic,anonymous,lrr,lrsj=now()";
			LinkedList<SQLBean> beanList = new LinkedList<SQLBean>();
			String infoSql = DBTools.getInsertSql(insertField, insertTable);
			String[] infoParam = DBTools.getInsertParms(insertField, infoJson);
			SQLBean infoBean = new SQLBean(infoSql, infoParam);
			beanList.add(infoBean);
			//表格插入部分
			JSONArray insertArray = json.getJSONArray("insertArray");
			if(insertArray.size() > 0){
				String insertArrayField = "id,label,label_name";
				String arrayInsertSql = DBTools.getInsertSql(insertArrayField, arrayTable);
				List<String[]> arrayInsertParams = DBTools.getInsertListParms(insertArrayField, insertArray);	
				SQLBean insertArrayBean = new SQLBean(arrayInsertSql, arrayInsertParams);
				beanList.add(insertArrayBean);
			}
			//表格插入部分
			JSONArray starArray = json.getJSONArray("starArray");
			if(starArray.size() > 0){
				String insertArrayField = "id,star,starname,starvalue";
				String arrayInsertSql = DBTools.getInsertSql(insertArrayField, starTable);
				List<String[]> arrayInsertParams = DBTools.getInsertListParms(insertArrayField, starArray);	
				SQLBean insertArrayBean = new SQLBean(arrayInsertSql, arrayInsertParams);
				beanList.add(insertArrayBean);
			}
			DBTools.executeBatch(beanList);
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_Add_Data_Success); 
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Add_Data_Error);
		}
		return response;
	}
	@Override
	public JSONObject get_label(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
        String selectSB = " select * from ( " + 
        		"(select count(id)sum from pingjia_info where pjId = '"+json.getString("userId")+"')a, " + 
        		"( " + 
        		"select label,label_name,count(label)Lsum from " + 
        		"(select a.id,pjId,b.label,label_name from pingjia_info a LEFT JOIN pingjia_label_detail b on a.id = b.id where pjId = '"+json.getString("userId")+"')a GROUP BY label ORDER BY Lsum desc )b " + 
        		") ";
        String headField = "*";
		SearchResult  searchResult = new SearchResult();
		JSONObject mergeJson = new JSONObject();
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject getpingjia(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
		String typeString="";
		String contentString="";
		String labelString="";
		if(!json.getString("type").equals("all") && !json.getString("type").equals("")){
			if(json.getString("type").equals("positive")) {
				typeString = " and star >= 4";
			}else {
				typeString = " and star <= 3";
			}
		}
		if( !json.getString("content").equals("")){
			contentString = " and content like '%"+json.getString("content")+"%'";
		}
		if( !json.getString("labelid").equals("")){
			labelString = " and b.label = '"+json.getString("labelid")+"'";
		}
        String selectSB = "select * from ( " + 
        		"select a.id,c.name lrr,lrsj,star,content,pic,a.anonymous,b.label,c.photo from "
        		+ "pingjia_info a "
        		+ " LEFT JOIN "
        		+ " pingjia_label_detail b on a.id=b.id"
        		+ " LEFT JOIN"
        		+ " (select * from t_user where username = '"+json.getString("userId")+"' limit 1) c ON a.lrr = c.username where "
        		+ "pjId = '"+json.getString("userId")+"'  " +labelString+ 
        		")a where 1=1 "+typeString+contentString+
        		 " GROUP BY id";
        System.err.println("-*---"+selectSB);
        String headField = "*";
		SearchResult  searchResult = new SearchResult();
		JSONObject mergeJson = new JSONObject();
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject getstardiv(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
        String selectSB = "select id,type,name from dict_star where flag = 0 ";
        String headField = "*";
		SearchResult  searchResult = new SearchResult();
		JSONObject mergeJson = new JSONObject();
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject getstar(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
        String selectSB = "select ROUND(AVG(star),0)star from pingjia_info where pjId = '"+json.getString("userId")+"' ";
        String headField = "*";
		SearchResult  searchResult = new SearchResult();
		JSONObject mergeJson = new JSONObject();
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject get_department(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
        String selectSB = "select groupId,name,state,path from t_group where groupId  = '"+json.getString("pId")+"'";
//        System.err.println(selectSB);
        String headField = "*";
		SearchResult  searchResult = new SearchResult();
		JSONObject mergeJson = new JSONObject();
		String asc = null;
		String ascField = "";
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject get_video(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
		String idstring="";
		String datestring="";
		String shopcodestring="";
		if(json.getString("id")!= null && !json.getString("id").equals("")) {
			idstring = " and id = "+json.getInteger("id");
		}
		if(json.getString("startdate")!= null && !json.getString("startdate").equals("")) {
			datestring = " and uploaddate >= '"+json.getString("startdate")+" 00:00:00' and uploaddate <= '"+json.getString("enddate")+" 23:59:59'";
		}
		if(json.getString("shopcode")!= null && !json.getString("shopcode").equals("") && !json.getString("shopcode").equals("0")) {
			shopcodestring = " and a.shopcode = '"+json.getString("shopcode")+"'";
		}
        String selectSB = " select a.*,b.deptname from sixiang_video a "
        		+ "LEFT JOIN jhcvs.dict_department b on a.shopcode = b.deptcode  "
        		+ " where 1=1 "+idstring+datestring+shopcodestring;
        System.out.println(selectSB+"----");
        
        String headField = "*";
		SearchResult  searchResult = new SearchResult();
		JSONObject mergeJson = new JSONObject();
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject get_today(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
        String selectSB = " select * from sixiang_video where shopcode = '"+json.getString("shopcode")+"' and  TO_DAYS(uploaddate) = TO_DAYS(NOW())" ;

        String headField = "*";
		SearchResult  searchResult = new SearchResult();
		JSONObject mergeJson = new JSONObject();
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject save_video(JSONObject json, JSONObject response) {
    	// 先查询主键是否存在
    	try {	

			String insertField = "shopcode,video,lrr,uploaddate=now()";
			DBTools.insert(insertField, "sixiang_video",json);
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_Add_Data_Success);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Add_Data_Error);
		}
		return response;
	}
	@Override
	public JSONObject del_video(JSONObject json, JSONObject response) {
		
		String whereField = "id";
    	String delTable = "sixiang_video";
    	try {
			DBTools2.delete(whereField, delTable,json);
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_Del_Data_Success);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Del_Data_Error);
		}
		return response;
	}
	@Override
	public JSONObject get_shop(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
		
        String selectSB = " select deptcode,deptname from jhcvs.dict_department where deptcode = '"+json.getString("shopcode")+"' ";
        String headField = "*";
		SearchResult  searchResult = new SearchResult();
		JSONObject mergeJson = new JSONObject();
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
}

